<?php

    require_once '../lib/maikuro/db/SqlitePdoDbHandler.php';
    require_once '../lib/maikuro/db/DbHelper.php';

    $dbHandler = new SqlitePdoDbHandler(realpath('./src/etc') . '/example.sqlite');

    // Set up some example data in the form of column => value
    $exampleData = array(
        array('exampleColumn' => 'Example data 1'),
        array('exampleColumn' => 'Example data 2'),
    );

    // Create a database transaction which allows for multiple queries to be
    // executed in the same transaction.
    $transaction = $dbHandler->createTransaction();
    foreach ($exampleData as $values) {
        $transaction->addQuery('INSERT INTO exampleTable (exampleColumn) VALUES (?)', $values);
    }
    // Commit the transaction and the result is returned. If multiple queries
    // are executed, the result will be an array of DbResult objects. If a
    // a single query is executed a DbResult object will be returned.
    $result = $transaction->commit();

    // The DbHelper requires an IDbHandler, in this case we're using the SQLite
    // PDO handler.
    DbHelper::setDbHandler($dbHandler);

    // Insert a single row into the database.
    $result = DbHelper::insert('exampleTable', array('exampleColumn' => 'Example data 3'));

    // Get the last insert id:
    echo 'The last insert id is: ' . $result->getLastInsertId() . '<br />';
    
    // This queries the database and returns a DbResult object which contains
    // a row set.
    $result = DbHelper::select('*', 'exampleTable');

    // The DbResult class implements the Iterator interface so iterating over
    // the row set could be done like this:
    foreach ($result as $row) {
        echo 'id: ' . $row->id . ' exampleColumn: ' . $row->exampleColumn . '<br />';
    }

    // ...or like this:
    foreach ($result->getRowSet() as $row) {
        echo 'id: ' . $row->id . ' exampleColumn: ' . $row->exampleColumn . '<br />';
    }
    
    // The DbResult also implements the Countable interface:
    echo 'The DbResult row set size is ' . count($result) . ' rows.';

    // Delete the example data:
    $result = DbHelper::delete('exampleTable');

    // The DbResult contains the number of affected rows:
    echo 'The number of affected rows is: ' . $result->getAffectedRows();
